package common;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import db.DBManager;

public class Payment 
{
	private int id;
	private String num;						//納品番号
	private String order_num;			//受注番号
	private String customer_code;	//顧客コード
	private Date date;						//請求日
	private int cost_total;					//合計原価
	private int price_total;					//合計売価
	private int fee;								//運賃
	private Date deliver;					//配達完了日
	private Date receipt_date;			//納品書発行日
	
	private String name;
	private String card;
	
	public static List<Payment> find_not_yet()
	{
		Connection con = DBManager.getConnection();
		List<Payment> list = new ArrayList<Payment>();
		try
		{
			String sql = "select payment.num,"
					+ " customer.name,"
					+ " customer.card,"
					+ " payment.price_total,"
					+ " payment.fee,"
					+ " payment.receipt_date"
					+ " from payment inner join customer"
					+ " on payment.customer_code = customer.code"
					+ " where payment_flag = 0"
					+ " order by payment.num;";
			Statement stm = con.createStatement();
			ResultSet rs = stm.executeQuery(sql);
			while(rs.next())
			{
				Payment p = new Payment();
				p.setNum(rs.getString("num"));															//納品番号
				p.setName(rs.getString("name"));														//顧客名
				p.setCard(rs.getString("card"));															//カード番号
				p.setPrice_total((rs.getInt("fee")) + rs.getInt("price_total"));			//請求金額
				p.setReceipt_date(rs.getDate("receipt_date"));		
				list.add(p);
			}
			rs.close();
			stm.close();
			con.close();
		}
		catch(SQLException e)
		{
			e.printStackTrace();
		}
		return list;
	}
	
	public boolean setPaid()
	{
		Connection con = DBManager.getConnection();
		boolean ret = false;
		try
		{
			String sql = "update payment"
					+ " set payment_flag = 1"
					+ " where num =" + this.num;		//納品番号
			Statement stm = con.createStatement();
			stm.executeUpdate(sql);
			ret = true;
			
			stm.close();
			con.close();
		}
		catch(SQLException e)
		{
			e.printStackTrace();
		}
		return ret;
	}
	
	/**
	 * 納品番号と配達完了日を受け取り、納品番号でreceiptテーブルを検索し受注情報を取得し
	 * 配達完了データとしてpaymentテーブルに登録する
	 * @param num
	 * @param date
	 * @return List<payment>
	 */
	public static List<Payment> insert_payment(String num, Date date)	//納品番号と配達完了日を受け取る
	{
		Connection con = DBManager.getConnection();
		List<Payment> list = new ArrayList<Payment>();
		try
		{
			String sql = "select receipt.*, customer.card "
					+ "from receipt inner join customer "
					+ "on customer.code = receipt.customer_code "
					+ "where receipt.num ="+ num;		//納品番号でreceiptテーブル、customerテーブル検索
			Statement smt = con.createStatement();
			ResultSet rs = smt.executeQuery(sql);
			if(rs.next())		//ヒットしたデータを取得
			{
				Payment p = new Payment();
				p.setOrder_num(rs.getString("order_num"));
				p.setCustomer_code(rs.getString("customer_code"));
				p.setDate(new Date());
				p.setReceipt_date(rs.getDate("date"));
				p.setCost_total(rs.getInt("cost_total"));
				p.setPrice_total(rs.getInt("price_total"));
				p.setFee(rs.getInt("fee"));
				p.setDeliver(date);
				p.setCard(rs.getString("card"));
				list.add(p);
				String insert_sql = "insert into payment"		//取得したデータをpaymentテーブルに登録
						+ " (num, order_num, customer_code, date, cost_total, price_total, fee, delivered, payment_flag, receipt_date) "
						+ "values ( ?,?,?,?,?,?,?,?,?,?)";
				int pay = 0;
				PreparedStatement ps = con.prepareStatement(insert_sql);
				ps.setString(1, num);
				ps.setString(2, p.getOrder_num());
				ps.setString(3, p.getCustomer_code());
				ps.setDate(4, new java.sql.Date(p.getDate().getTime()));
				ps.setInt(5, p.getCost_total());
				ps.setInt(6, p.getPrice_total());
				ps.setInt(7, p.getFee());
				ps.setDate(8, new java.sql.Date(date.getTime()));
				ps.setInt(9, pay);
				ps.setDate(10, new java.sql.Date(p.getReceipt_date().getTime()));
//				System.out.println(num+","		中身チェック
//						+ p.getOrder_num()+","
//						+ p.getCustomer_code()+","
//						+ p.getDate()+","
//						+ p.getCost_total()+","
//						+ p.getPrice_total()+","
//						+ p.getFee()+","
//						+ date+","
//						+ 0+","
//						+ p.getReceipt_date());
				ps.executeUpdate();
			}
			rs.close();
			smt.close();
			con.close();
		}
		catch(SQLException e)
		{
			e.printStackTrace();
		}
		return list;
	}
	
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getNum() {
		return num;
	}
	public void setNum(String num) {
		this.num = num;
	}
	public String getOrder_num() {
		return order_num;
	}
	public void setOrder_num(String order_num) {
		this.order_num = order_num;
	}
	public String getCustomer_code() {
		return customer_code;
	}
	public void setCustomer_code(String customer_code) {
		this.customer_code = customer_code;
	}
	public Date getDate() {
		return date;
	}
	public void setDate(Date date) {
		this.date = date;
	}
	public int getCost_total() {
		return cost_total;
	}
	public void setCost_total(int cost_total) {
		this.cost_total = cost_total;
	}
	public int getPrice_total() {
		return price_total;
	}
	public void setPrice_total(int price_total) {
		this.price_total = price_total;
	}
	public int getFee() {
		return fee;
	}
	public void setFee(int fee) {
		this.fee = fee;
	}
	public Date getDeliver() {
		return deliver;
	}
	public void setDeliver(Date deliver) {
		this.deliver = deliver;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public String getCard() {
		return card;
	}

	public void setCard(String card) {
		this.card = card;
	}

	public Date getReceipt_date() {
		return receipt_date;
	}

	public void setReceipt_date(Date receipt_date) {
		this.receipt_date = receipt_date;
	}
	
}
